using AuthService.Domain.Common;
using AuthService.Domain.Repositories;
using Dapper;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Npgsql;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Reflection;
using System.Text;

namespace AuthService.Infrastructure.Repositories;

/// <summary>
/// 通用仓储基类
/// 提供基础的CRUD操作和查询功能
/// </summary>
/// <typeparam name="TEntity">实体类型</typeparam>
/// <typeparam name="TKey">主键类型</typeparam>
public abstract class BaseRepository<TEntity, TKey> : IBaseRepository<TEntity, TKey>
    where TEntity : BaseEntity, new()
{
    protected readonly string _connectionString;
    protected readonly ILogger _logger;
    protected readonly string _tableName;
    protected readonly string _primaryKeyName;

    /// <summary>
    /// 构造函数
    /// </summary>
    /// <param name="configuration">配置</param>
    /// <param name="logger">日志记录器</param>
    protected BaseRepository(IConfiguration configuration, ILogger logger)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection")
            ?? throw new ArgumentException("数据库连接字符串未配置");
        _logger = logger;

        // 获取表名和主键名
        _tableName = GetTableName();
        _primaryKeyName = GetPrimaryKeyName();
    }

    /// <summary>
    /// 创建数据库连接
    /// </summary>
    /// <returns>数据库连接</returns>
    protected virtual IDbConnection CreateConnection()
    {
        return new NpgsqlConnection(_connectionString);
    }

    /// <summary>
    /// 获取表名
    /// </summary>
    /// <returns>表名</returns>
    protected virtual string GetTableName()
    {
        var tableAttribute = typeof(TEntity).GetCustomAttribute<TableAttribute>();
        return tableAttribute?.Name ?? typeof(TEntity).Name + "s";
    }

    /// <summary>
    /// 获取主键名
    /// </summary>
    /// <returns>主键名</returns>
    protected virtual string GetPrimaryKeyName()
    {
        return "Id"; // 默认主键名为Id
    }

    #region 基础CRUD操作

    /// <summary>
    /// 根据ID获取实体
    /// </summary>
    public virtual async Task<TEntity?> GetByIdAsync(TKey id, CancellationToken cancellationToken = default)
    {
        var sql = $"SELECT * FROM {_tableName} WHERE {_primaryKeyName} = @Id AND \"IsDeleted\" = false";

        try
        {
            using var connection = CreateConnection();
            var entity = await connection.QueryFirstOrDefaultAsync<TEntity>(sql, new { Id = id });
            return entity;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "根据ID获取实体失败，表: {TableName}, ID: {Id}", _tableName, id);
            throw;
        }
    }

    /// <summary>
    /// 获取所有实体
    /// </summary>
    public virtual async Task<IEnumerable<TEntity>> GetAllAsync(CancellationToken cancellationToken = default)
    {
        var sql = $"SELECT * FROM {_tableName} WHERE \"IsDeleted\" = false ORDER BY \"CreatedAt\" DESC";

        try
        {
            using var connection = CreateConnection();
            var entities = await connection.QueryAsync<TEntity>(sql);
            return entities;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取所有实体失败，表: {TableName}", _tableName);
            throw;
        }
    }

    /// <summary>
    /// 创建实体
    /// </summary>
    public virtual async Task<TEntity> CreateAsync(TEntity entity, CancellationToken cancellationToken = default)
    {
        // 设置审计字段
        entity.Id = entity.Id == Guid.Empty ? Guid.NewGuid() : entity.Id;
        entity.CreatedAt = DateTime.UtcNow;
        entity.UpdatedAt = DateTime.UtcNow;
        entity.IsDeleted = false;

        var (sql, parameters) = BuildInsertSql(entity);

        try
        {
            using var connection = CreateConnection();
            await connection.ExecuteAsync(sql, parameters);

            _logger.LogInformation("创建实体成功，表: {TableName}, ID: {Id}", _tableName, entity.Id);
            return entity;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "创建实体失败，表: {TableName}", _tableName);
            throw;
        }
    }

    /// <summary>
    /// 更新实体
    /// </summary>
    public virtual async Task<TEntity> UpdateAsync(TEntity entity, CancellationToken cancellationToken = default)
    {
        // 设置审计字段
        entity.UpdatedAt = DateTime.UtcNow;

        var (sql, parameters) = BuildUpdateSql(entity);

        try
        {
            using var connection = CreateConnection();
            var affectedRows = await connection.ExecuteAsync(sql, parameters);

            if (affectedRows == 0)
            {
                throw new InvalidOperationException($"实体不存在或已被删除，ID: {entity.Id}");
            }

            _logger.LogInformation("更新实体成功，表: {TableName}, ID: {Id}", _tableName, entity.Id);
            return entity;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "更新实体失败，表: {TableName}, ID: {Id}", _tableName, entity.Id);
            throw;
        }
    }

    /// <summary>
    /// 删除实体（软删除）
    /// </summary>
    public virtual async Task<bool> DeleteAsync(TKey id, string? deletedBy = null, CancellationToken cancellationToken = default)
    {
        var sql = $@"
            UPDATE {_tableName} 
            SET IsDeleted = true, 
                DeletedAt = @DeletedAt, 
                DeletedBy = @DeletedBy,
                UpdatedAt = @UpdatedAt
            WHERE {_primaryKeyName} = @Id AND IsDeleted = false";

        try
        {
            using var connection = CreateConnection();
            var affectedRows = await connection.ExecuteAsync(sql, new
            {
                Id = id,
                DeletedAt = DateTime.UtcNow,
                DeletedBy = deletedBy,
                UpdatedAt = DateTime.UtcNow
            });

            var success = affectedRows > 0;
            if (success)
            {
                _logger.LogInformation("软删除实体成功，表: {TableName}, ID: {Id}", _tableName, id);
            }
            else
            {
                _logger.LogWarning("软删除实体失败，实体不存在，表: {TableName}, ID: {Id}", _tableName, id);
            }

            return success;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "软删除实体失败，表: {TableName}, ID: {Id}", _tableName, id);
            throw;
        }
    }

    /// <summary>
    /// 物理删除实体
    /// </summary>
    public virtual async Task<bool> HardDeleteAsync(TKey id, CancellationToken cancellationToken = default)
    {
        var sql = $"DELETE FROM {_tableName} WHERE {_primaryKeyName} = @Id";

        try
        {
            using var connection = CreateConnection();
            var affectedRows = await connection.ExecuteAsync(sql, new { Id = id });

            var success = affectedRows > 0;
            if (success)
            {
                _logger.LogInformation("物理删除实体成功，表: {TableName}, ID: {Id}", _tableName, id);
            }
            else
            {
                _logger.LogWarning("物理删除实体失败，实体不存在，表: {TableName}, ID: {Id}", _tableName, id);
            }

            return success;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "物理删除实体失败，表: {TableName}, ID: {Id}", _tableName, id);
            throw;
        }
    }

    #endregion

    #region 查询操作

    /// <summary>
    /// 根据条件查询
    /// </summary>
    public virtual async Task<IEnumerable<TEntity>> FindAsync(string whereClause, object? parameters = null, CancellationToken cancellationToken = default)
    {
        var sql = $"SELECT * FROM {_tableName} WHERE \"IsDeleted\" = false AND ({whereClause})";

        try
        {
            using var connection = CreateConnection();
            var entities = await connection.QueryAsync<TEntity>(sql, parameters);
            return entities;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "条件查询失败，表: {TableName}, 条件: {WhereClause}", _tableName, whereClause);
            throw;
        }
    }

    /// <summary>
    /// 根据条件查询单个实体
    /// </summary>
    public virtual async Task<TEntity?> FindFirstAsync(string whereClause, object? parameters = null, CancellationToken cancellationToken = default)
    {
        var sql = $"SELECT * FROM {_tableName} WHERE \"IsDeleted\" = false AND ({whereClause}) LIMIT 1";

        try
        {
            using var connection = CreateConnection();
            var entity = await connection.QueryFirstOrDefaultAsync<TEntity>(sql, parameters);
            return entity;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "条件查询单个实体失败，表: {TableName}, 条件: {WhereClause}", _tableName, whereClause);
            throw;
        }
    }

    /// <summary>
    /// 检查实体是否存在
    /// </summary>
    public virtual async Task<bool> ExistsAsync(string whereClause, object? parameters = null, CancellationToken cancellationToken = default)
    {
        var sql = $"SELECT 1 FROM {_tableName} WHERE IsDeleted = false AND ({whereClause}) LIMIT 1";

        try
        {
            using var connection = CreateConnection();
            var result = await connection.QueryFirstOrDefaultAsync<int?>(sql, parameters);
            return result.HasValue;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "检查实体存在性失败，表: {TableName}, 条件: {WhereClause}", _tableName, whereClause);
            throw;
        }
    }

    /// <summary>
    /// 获取数量
    /// </summary>
    public virtual async Task<int> CountAsync(string? whereClause = null, object? parameters = null, CancellationToken cancellationToken = default)
    {
        var sql = $"SELECT COUNT(*) FROM {_tableName} WHERE IsDeleted = false";
        if (!string.IsNullOrEmpty(whereClause))
        {
            sql += $" AND ({whereClause})";
        }

        try
        {
            using var connection = CreateConnection();
            var count = await connection.QuerySingleAsync<int>(sql, parameters);
            return count;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取数量失败，表: {TableName}, 条件: {WhereClause}", _tableName, whereClause);
            throw;
        }
    }

    /// <summary>
    /// 分页查询
    /// </summary>
    public virtual async Task<(IEnumerable<TEntity> Items, int TotalCount)> GetPagedAsync(
        int pageNumber = 1,
        int pageSize = 20,
        string? whereClause = null,
        string? orderBy = null,
        object? parameters = null,
        CancellationToken cancellationToken = default)
    {
        var offset = (pageNumber - 1) * pageSize;
        var baseWhere = "\"IsDeleted\" = false";
        var fullWhere = string.IsNullOrEmpty(whereClause) ? baseWhere : $"{baseWhere} AND ({whereClause})";
        var orderByClause = string.IsNullOrEmpty(orderBy) ? "\"CreatedAt\" DESC" : orderBy;

        var dataSql = $@"
            SELECT * FROM {_tableName}
            WHERE {fullWhere}
            ORDER BY {orderByClause}
            LIMIT @PageSize OFFSET @Offset";

        var countSql = $"SELECT COUNT(*) FROM {_tableName} WHERE {fullWhere}";

        try
        {
            using var connection = CreateConnection();

            var queryParams = new DynamicParameters(parameters);
            queryParams.Add("PageSize", pageSize);
            queryParams.Add("Offset", offset);

            var items = await connection.QueryAsync<TEntity>(dataSql, queryParams);
            var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);

            return (items, totalCount);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "分页查询失败，表: {TableName}", _tableName);
            throw;
        }
    }

    #endregion

    #region 批量操作

    /// <summary>
    /// 批量创建
    /// </summary>
    public virtual async Task<IEnumerable<TEntity>> CreateBatchAsync(IEnumerable<TEntity> entities, CancellationToken cancellationToken = default)
    {
        var entityList = entities.ToList();
        if (!entityList.Any()) return entityList;

        // 设置审计字段
        var now = DateTime.UtcNow;
        foreach (var entity in entityList)
        {
            entity.Id = entity.Id == Guid.Empty ? Guid.NewGuid() : entity.Id;
            entity.CreatedAt = now;
            entity.UpdatedAt = now;
            entity.IsDeleted = false;
        }

        var (sql, _) = BuildInsertSql(entityList.First());

        try
        {
            using var connection = CreateConnection();
            await connection.ExecuteAsync(sql, entityList);

            _logger.LogInformation("批量创建实体成功，表: {TableName}, 数量: {Count}", _tableName, entityList.Count);
            return entityList;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "批量创建实体失败，表: {TableName}", _tableName);
            throw;
        }
    }

    /// <summary>
    /// 批量更新
    /// </summary>
    public virtual async Task<IEnumerable<TEntity>> UpdateBatchAsync(IEnumerable<TEntity> entities, CancellationToken cancellationToken = default)
    {
        var entityList = entities.ToList();
        if (!entityList.Any()) return entityList;

        // 设置审计字段
        var now = DateTime.UtcNow;
        foreach (var entity in entityList)
        {
            entity.UpdatedAt = now;
        }

        var (sql, _) = BuildUpdateSql(entityList.First());

        try
        {
            using var connection = CreateConnection();
            await connection.ExecuteAsync(sql, entityList);

            _logger.LogInformation("批量更新实体成功，表: {TableName}, 数量: {Count}", _tableName, entityList.Count);
            return entityList;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "批量更新实体失败，表: {TableName}", _tableName);
            throw;
        }
    }

    /// <summary>
    /// 批量删除（软删除）
    /// </summary>
    public virtual async Task<int> DeleteBatchAsync(IEnumerable<TKey> ids, string? deletedBy = null, CancellationToken cancellationToken = default)
    {
        var idList = ids.ToList();
        if (!idList.Any()) return 0;

        var sql = $@"
            UPDATE {_tableName}
            SET IsDeleted = true,
                DeletedAt = @DeletedAt,
                DeletedBy = @DeletedBy,
                UpdatedAt = @UpdatedAt
            WHERE {_primaryKeyName} = ANY(@Ids) AND IsDeleted = false";

        try
        {
            using var connection = CreateConnection();
            var affectedRows = await connection.ExecuteAsync(sql, new
            {
                Ids = idList.ToArray(),
                DeletedAt = DateTime.UtcNow,
                DeletedBy = deletedBy,
                UpdatedAt = DateTime.UtcNow
            });

            _logger.LogInformation("批量软删除实体成功，表: {TableName}, 数量: {Count}", _tableName, affectedRows);
            return affectedRows;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "批量软删除实体失败，表: {TableName}", _tableName);
            throw;
        }
    }

    #endregion

    #region 事务操作

    /// <summary>
    /// 在事务中执行操作
    /// </summary>
    public virtual async Task<T> ExecuteInTransactionAsync<T>(Func<Task<T>> operation, CancellationToken cancellationToken = default)
    {
        using var connection = new NpgsqlConnection(_connectionString);
        await connection.OpenAsync(cancellationToken);

        using var transaction = await connection.BeginTransactionAsync(cancellationToken);
        try
        {
            var result = await operation();
            await transaction.CommitAsync(cancellationToken);
            return result;
        }
        catch
        {
            await transaction.RollbackAsync(cancellationToken);
            throw;
        }
    }

    #endregion

    #region 辅助方法

    /// <summary>
    /// 构建插入SQL
    /// </summary>
    protected virtual (string Sql, object Parameters) BuildInsertSql(TEntity entity)
    {
        var properties = typeof(TEntity).GetProperties()
            .Where(p => p.CanRead && p.GetCustomAttribute<NotMappedAttribute>() == null)
            .ToList();

        var columns = string.Join(", ", properties.Select(p => $"\"{p.Name}\""));
        var values = string.Join(", ", properties.Select(p => $"@{p.Name}"));

        var sql = $"INSERT INTO {_tableName} ({columns}) VALUES ({values})";
        return (sql, entity);
    }

    /// <summary>
    /// 构建更新SQL
    /// </summary>
    protected virtual (string Sql, object Parameters) BuildUpdateSql(TEntity entity)
    {
        var properties = typeof(TEntity).GetProperties()
            .Where(p => p.CanRead && p.CanWrite &&
                       p.Name != _primaryKeyName &&
                       p.Name != "CreatedAt" &&
                       p.GetCustomAttribute<NotMappedAttribute>() == null)
            .ToList();

        var setClause = string.Join(", ", properties.Select(p => $"\"{p.Name}\" = @{p.Name}"));
        var sql = $"UPDATE {_tableName} SET {setClause} WHERE {_primaryKeyName} = @{_primaryKeyName} AND \"IsDeleted\" = false";

        return (sql, entity);
    }

    #endregion
}
